﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using System.Reflection;
using XQ.Framework.core;
using XQ.Framework.model;

namespace lib
{
    /// <summary>
    /// Summary description for DbHelper
    /// </summary>
    public class DBHelper
    {
        #region 方法
        public static int ExecuteSQL(string sql)
        {
            return 0;
        }

        public static void Release(IDbConnection con)
        {
            try
            {
                if (con != null && con.State == ConnectionState.Open) { con.Close(); con.Dispose(); }
            }
            catch { }
        }
        public static void Release(IDbCommand cmd)
        {
            try
            {
                if (cmd != null) { cmd.Dispose(); }
            }
            catch { }
        }
        public static void Release(IDataReader reader)
        {
            try
            {
                if (reader != null) { reader.Close(); reader.Dispose(); }
            }
            catch { }
        }
        public static void Release(IDbTransaction trans)
        {
            try
            {
                if (trans != null) { trans.Dispose(); }
            }
            catch { }
        }
        public static void Release(IDbConnection con, IDbCommand cmd, IDataReader reader)
        {
            Release(reader); Release(cmd); Release(con);
        }

        public static List<string> GetKeys(IDataReader reader)
        {
            List<string> list = new List<string>();
            if (reader != null)
            {
                for (int i = 0; i < reader.FieldCount; ++i)
                {
                    list.Add(reader.GetName(i));
                }
            }
            return list;
        }

        public static List<string> GetKeys(DataTable tb)
        {
            List<string> list = new List<string>();
            if (tb != null)
            {
                for (int i = 0; i < tb.Columns.Count; ++i)
                {
                    list.Add(tb.Columns[i].ColumnName);
                }
            }
            return list;
        }

        /// <summary>
        /// 通过反射把reader对象映射到实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static List<T> ReaderToEntity<T>(IDataReader reader) where T : class,new()
        {
            List<T> list = new List<T>();
            if (reader == null) { return list; }
            List<string> keys = GetKeys(reader);
            List<PropertyInfo> props = typeof(T).GetProperties().ToList();
            while (reader != null && reader.Read())
            {
                T obj = new T();
                keys.ForEach(delegate(string key) 
                {
                    PropertyInfo prop = props.Find(x=>x.Name==key);
                    if (prop == null) { return; }
                    prop.SetValue(obj, reader[key], null);
                });
                list.Add(obj);
            }
            return list;
        }
        #endregion

        #region 生成sql语句和参数
        public static SqlDataHolder CreateWhereSqlAndParam(List<ConditionModel> conditions, Dictionary<string, object> param)
        {
            SqlDataHolder holder = new SqlDataHolder();
            holder.Sql = string.Empty;
            holder.Param = param == null ? new Dictionary<string, object>() : param;
            if (!ValidateHelper.IsPlumpList(conditions))
            {
                return holder;
            }
            holder.Sql += "where ";
            bool IsEmptyCondition = true; int Error = 0, Index = 0;
            conditions.ForEach(delegate(ConditionModel model)
            {
                //查询时一个字段可能多次使用，为了不让参数名重复，添加index来区别
                ++Index;
                if (model == null) { ++Error; return; }
                //检查必要参数
                if (!ValidateHelper.IsPlumpString(model.Key)) { ++Error; return; }
                //如果是比较
                if (model.Operator == Operators.Equal || model.Operator == Operators.NotEqual ||
                    model.Operator == Operators.Bigger || model.Operator == Operators.BiggerOrEqual ||
                    model.Operator == Operators.Smaller || model.Operator == Operators.SmallerOrEqual ||
                    model.Operator == Operators.Is)
                {
                    // "[and,or] key [=,>,<,<>] @value "
                    holder.Sql += string.Format("{0}{1} {2} {3} ",
                        IsEmptyCondition ? string.Empty : (model.ConnectorStr() + " "),
                        model.Key,
                        model.OperatorStr(),
                        "@PARAM_" + Index);
                    holder.Param.Add("@PARAM_" + Index, model.FirstValue());

                    IsEmptyCondition = false;
                }
                //如果是范围
                if (model.Operator == Operators.In || model.Operator == Operators.NotIn)
                {
                    if (!ValidateHelper.IsPlumpList(model.Values))
                    {
                        return;
                    }
                    // "[and,or] key [in,not in] (@v1,@v2) "
                    holder.Sql += string.Format("{0}{1} {2} (",
                        IsEmptyCondition ? string.Empty : (model.ConnectorStr() + " "),
                        model.Key,
                        model.OperatorStr());
                    for (int i = 0; i < model.Values.Count; ++i)
                    {
                        holder.Sql += (i == 0 ? string.Empty : ",");
                        //用index和i来参与参数命名，保证唯一性
                        holder.Sql += "@PARAM_" + Index + "_" + i;
                        holder.Param.Add("@PARAM_" + Index + "_" + i, model.Values[i]);
                    }
                    holder.Sql += ") ";

                    IsEmptyCondition = false;
                }
                //如果是模糊查询
                if (model.Operator == Operators.Like || model.Operator == Operators.NotLike)
                {
                    // "[and,or] key [like,not like] '%@value%' "
                    holder.Sql += string.Format("{0}{1} {2} '%{3}%' ",
                        IsEmptyCondition ? string.Empty : (model.ConnectorStr() + " "),
                        model.Key,
                        model.OperatorStr(),
                        "@PARAM_" + Index);
                    holder.Param.Add("@PARAM_" + Index, model.FirstValue());

                    IsEmptyCondition = false;
                }
            });
            if (IsEmptyCondition) { holder.Sql = string.Empty; }
            if (Error != 0)
            {
                throw new Exception("所给参数不正确（key或者value不是有效值）");
            }
            return holder;
        }
        /// <summary>
        /// 生成插入sql和参数
        /// </summary>
        /// <param name="table_name"></param>
        /// <param name="data_dict"></param>
        /// <returns></returns>
        public static SqlDataHolder CreateInsertSqlAndParam(string table_name, Dictionary<string, object> data_dict)
        {
            SqlDataHolder hodler = new SqlDataHolder();
            hodler.Sql = string.Empty;
            hodler.Param = new Dictionary<string, object>();
            try
            {
                if (!ValidateHelper.IsPlumpString(table_name) || !ValidateHelper.IsPlumpDict(data_dict))
                {
                    hodler.Error = "table and data dict is must";
                    return hodler;
                }
                //INSERT
                List<string> keys = data_dict.Keys.ToList();

                hodler.Sql = string.Format("insert into {0} (", table_name);
                hodler.Sql += StringHelper.Join(",", keys);
                hodler.Sql += ") value (";
                //VALUE
                int index = 0;
                keys.ForEach(delegate(string key)
                {
                    //sql
                    hodler.Sql += (index == 0 ? string.Empty : ",");
                    hodler.Sql += string.Format("@P_{0}", key.ToUpper());
                    //param
                    hodler.Param.Add(string.Format("@P_{0}", key.ToUpper()), data_dict[key]);
                    ++index;
                });
                hodler.Sql += ")";
                return hodler;
            }
            catch (Exception e)
            {
                hodler.Error = e.Message;
                return hodler;
            }
        }
        /// <summary>
        /// 生成删除sql和参数
        /// </summary>
        /// <param name="table_name"></param>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public static SqlDataHolder CreateDeleteSqlAndParam(string table_name, List<ConditionModel> conditions)
        {
            SqlDataHolder hodler = new SqlDataHolder();
            hodler.Sql = string.Empty;
            hodler.Param = new Dictionary<string, object>();
            try
            {
                if (!ValidateHelper.IsPlumpString(table_name))
                {
                    hodler.Error = "table name is must";
                    return hodler;
                }
                hodler.Sql = string.Format("delete from {0} ", table_name);
                SqlDataHolder where_holder = DBHelper.CreateWhereSqlAndParam(conditions, hodler.Param);
                hodler.Sql += where_holder.Sql;
                hodler.Param = where_holder.Param;
                return hodler;
            }
            catch (Exception e)
            {
                hodler.Error = e.Message;
                return hodler;
            }
        }
        /// <summary>
        /// 生成更新sql语句和参数
        /// </summary>
        /// <param name="table_name"></param>
        /// <param name="data_dict"></param>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public static SqlDataHolder CreateUpdateSqlAndParam(string table_name, Dictionary<string, object> data_dict,
            List<ConditionModel> conditions)
        {
            SqlDataHolder hodler = new SqlDataHolder();
            hodler.Sql = string.Empty;
            hodler.Param = new Dictionary<string, object>();
            try
            {
                if (!ValidateHelper.IsPlumpString(table_name) || !ValidateHelper.IsPlumpDict(data_dict))
                {
                    hodler.Error = "table_name and data_dict is must";
                    return hodler;
                }
                hodler.Sql = string.Format("update {0} set ", table_name);
                int index = 0;
                data_dict.Keys.ToList().ForEach(delegate(string key)
                {
                    hodler.Sql += (index == 0 ? "" : ",");
                    hodler.Sql += key + "=" + string.Format("@P_{0}", key.ToUpper());
                    hodler.Param.Add(string.Format("@P_{0}", key.ToUpper()), data_dict[key]);
                    ++index;
                });
                hodler.Sql += " ";
                SqlDataHolder whereHolder = DBHelper.CreateWhereSqlAndParam(conditions, hodler.Param);
                hodler.Sql += whereHolder.Sql;
                hodler.Param = whereHolder.Param;
                return hodler;
            }
            catch (Exception e)
            {
                hodler.Error = e.Message;
                return hodler;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="table_name"></param>
        /// <param name="select_list"></param>
        /// <param name="conditions"></param>
        /// <param name="OrderBy"></param>
        /// <param name="Limit_min"></param>
        /// <param name="Limit_len"></param>
        /// <returns></returns>
        public static SqlDataHolder CreateQuerySqlAndParam(string table_name, List<string> select_list,
            List<ConditionModel> conditions, string OrderBy, string Limit_min, string Limit_len)
        {
            return CreateQuerySqlAndParam(table_name, select_list, conditions, new ConditionModel()
            {
                OrderBy = OrderBy,
                LimitMin = Limit_min,
                LimitLen = Limit_len
            });
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="table_name"></param>
        /// <param name="select_list"></param>
        /// <param name="conditions"></param>
        /// <param name="otherCondition"></param>
        /// <returns></returns>
        public static SqlDataHolder CreateQuerySqlAndParam(string table_name, List<string> select_list,
            List<ConditionModel> conditions, ConditionModel otherCondition)
        {
            SqlDataHolder hodler = new SqlDataHolder();
            hodler.Sql = string.Empty;
            hodler.Param = new Dictionary<string, object>();
            try
            {
                if (!ValidateHelper.IsPlumpString(table_name) || !ValidateHelper.IsPlumpList(select_list))
                {
                    hodler.Error = "table name and select list is must";
                    return hodler;
                }
                hodler.Sql = string.Format("select {0} from {1} ", StringHelper.Join(",", select_list), table_name);
                SqlDataHolder condition = CreateWhereSqlAndParam(conditions, hodler.Param);
                hodler.Sql += condition.Sql;
                hodler.Param = condition.Param;
                //如果没有othercondition就直接返回
                if (otherCondition == null) { return hodler; }
                // [group by xx]
                if (ValidateHelper.IsPlumpString(otherCondition.GroupBy))
                {
                    hodler.Sql += string.Format("group by {0} ", otherCondition.GroupBy);
                }
                // [order by xx desc ]
                if (ValidateHelper.IsPlumpString(otherCondition.OrderBy))
                {
                    hodler.Sql += string.Format("order by {0} ", otherCondition.OrderBy);
                }
                // [limit 0,100 ]
                if (ValidateHelper.IsAllPlumpString(otherCondition.LimitMin, otherCondition.LimitLen))
                {
                    hodler.Sql += string.Format("limit {0},{1} ", otherCondition.LimitMin, otherCondition.LimitLen);
                }

                return hodler;
            }
            catch (Exception e)
            {
                hodler.Error = e.Message;
                return hodler;
            }
        }
        #endregion

        #region 增删改查操作
        /// <summary>
        /// 
        /// </summary>
        /// <param name="db"></param>
        /// <param name="table_name"></param>
        /// <param name="data_dict"></param>
        /// <returns></returns>
        public static int Insert(IDataBase db, string table_name, Dictionary<string, object> data_dict)
        {
            try
            {
                if (db == null || !ValidateHelper.IsPlumpString(table_name) ||
                    !ValidateHelper.IsPlumpDict(data_dict))
                {
                    return 0;
                }
                SqlDataHolder hodler = DBHelper.CreateInsertSqlAndParam(table_name, data_dict);
                if (hodler.Error != null) { return 0; }
                return db.ExecuteNonQuery(hodler.Sql, hodler.Param);
            }
            catch (Exception e)
            {
                return 0;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="db"></param>
        /// <param name="table_name"></param>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public static int Delete(IDataBase db, string table_name, List<ConditionModel> conditions)
        {
            try
            {
                if (db == null || !ValidateHelper.IsPlumpString(table_name))
                {
                    return 0;
                }
                SqlDataHolder hodler = DBHelper.CreateDeleteSqlAndParam(table_name, conditions);
                if (hodler.Error != null) { return 0; }
                return db.ExecuteNonQuery(hodler.Sql, hodler.Param);
            }
            catch (Exception e)
            {
                return 0;
            }
        }
        public static int Update(IDataBase db, string table_name,
            Dictionary<string, object> data_dict, List<ConditionModel> conditions)
        {
            try
            {
                if (db == null || !ValidateHelper.IsPlumpString(table_name) ||
                    !ValidateHelper.IsPlumpDict(data_dict))
                {
                    return 0;
                }
                SqlDataHolder hodler = DBHelper.CreateUpdateSqlAndParam(table_name, data_dict, conditions);
                if (hodler.Error != null) { return 0; }
                return db.ExecuteNonQuery(hodler.Sql, hodler.Param);
            }
            catch (Exception e)
            {
                return 0;
            }
        }
        public static List<Dictionary<string, object>> Query(IDataBase db, string table_name, List<string> select_list,
            List<ConditionModel> conditions, string order_by, string limit_min, string limit_len)
        {
            return Query(db, table_name, select_list, conditions, new ConditionModel()
            {
                OrderBy = order_by,
                LimitMin = limit_min,
                LimitLen = limit_len
            });
        }
        public static List<Dictionary<string, object>> Query(IDataBase db, string table_name, List<string> select_list,
            List<ConditionModel> conditions, ConditionModel otherCondition)
        {
            IDataReader reader = null;
            try
            {
                if (db == null || !ValidateHelper.IsPlumpString(table_name)
                    || !ValidateHelper.IsPlumpList(select_list)) { return null; }
                SqlDataHolder hodler = CreateQuerySqlAndParam(table_name, select_list, conditions, otherCondition);
                if (hodler.Error != null) { return null; }
                reader = db.ExecuteReader(hodler.Sql, hodler.Param);
                List<Dictionary<string, object>> data = new List<Dictionary<string, object>>();
                Dictionary<string, object> row = null;
                int read_count = 0;
                while ((++read_count) <= 1000 && reader != null && reader.Read())
                {
                    row = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; ++i)
                    {
                        row.Add(reader.GetName(i), reader[reader.GetName(i)]);
                    }

                    data.Add(row);
                }
                return data;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                Release(reader);
            }
        }
        /// <summary>
        /// 查询第一行
        /// </summary>
        /// <param name="db"></param>
        /// <param name="table_name"></param>
        /// <param name="select_list"></param>
        /// <param name="where_dict"></param>
        /// <param name="order_by"></param>
        /// <param name="limit_min"></param>
        /// <param name="limit_len"></param>
        /// <returns></returns>
        public static Dictionary<string, object> QueryFirstRow(IDataBase db, string table_name, List<string> select_list,
            List<ConditionModel> conditions, string order_by, string limit_min, string limit_len)
        {
            List<Dictionary<string, object>> data = DBHelper.Query(db, table_name, select_list, conditions,
                order_by, limit_min, limit_len);
            return (data != null && data.Count > 0) ? data[0] : null;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="db"></param>
        /// <param name="table_name"></param>
        /// <param name="select_list"></param>
        /// <param name="where_dict"></param>
        /// <param name="order_by"></param>
        /// <param name="limit_min"></param>
        /// <param name="limit_len"></param>
        /// <returns></returns>
        public static object QueryFirstData(IDataBase db, string table_name, List<string> select_list,
            List<ConditionModel> conditions, string order_by, string limit_min, string limit_len)
        {
            Dictionary<string, object> first_row = QueryFirstRow(db, table_name, select_list, conditions,
                order_by, limit_min, limit_len);
            if (ValidateHelper.IsPlumpDict<string, object>(first_row))
            {
                return first_row[first_row.Keys.ToList()[0]];
            }
            return null;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="db"></param>
        /// <param name="table_name"></param>
        /// <param name="where_dict"></param>
        /// <param name="limit_min"></param>
        /// <param name="limit_len"></param>
        /// <returns></returns>
        public static int Count(IDataBase db, string table_name, List<ConditionModel> conditions,
            string limit_min, string limit_len)
        {
            return ConvertHelper.getInt(QueryFirstData(db, table_name, new List<string>() { "count(*)" },
                conditions, null, limit_min, limit_len), -1);
        }
        #endregion

    }
}